<?php
//创建PDO对象        (PDO是个类)
$db = new PDO("mysql:host=localhost",'root','root');
//exec()方法
$db -> exec('create database if not exists db3');
$db -> exec('use db3');
$db -> exec("create table if not exists students (xh varchar(9) not null primary key,name varchar(50),age int)");
//删
$r = $db -> exec('delete from students');
echo "删除记录个数:{$r}条\n";
//增
$r = $db -> exec("insert into students(xh,name,age) values
                                         ('213110601','张三',18),
                                         ('213110602','李四',22),
                                         ('213110603','王五',20),
                                         ('213110701','杨柳',19)");
echo "插入记录个数:{$r}条\n";
//改
$r = $db -> exec("update students set name = '杨六' where xh = '213110701'");
echo "更新记录个数:{$r}条\n";
//查          exec()不宜用于查询操作
$ps = $db -> query('select * from students limit 1');//返回的结果集包含着$ps语句对象中
foreach ($ps as $row){
    print_r($row);
}
//执行多条查询
echo "======================================一次执行多个查询=====================================\n";
$ps -> closeCursor();//关闭游标，使语句能再次被执行
$ps = $db -> query("select * from students where xh = '213110701';select * from students limit 3");
do{
    foreach ($ps as $row){
        echo "1.{$row['xh']}\t{$row['name']}\t{$row['age']}\n";
    }
    echo "--------------------------------------------\n";
}while($ps -> nextRowset());//获取下一个记录集，若存在，返回true
//禁用多条语句
echo "========================================利用query、execute方法进行查询===================================\n";
$ps = $db -> prepare("select * from students where age<?");//不包含占位符一般就用query()来进行查询
$ps -> execute([20]);//结果集包含着$ps语句对象中,数组中的数据对应于占位符，数组中的数据默认是字符串类型的数据
foreach ($ps as $row){
    echo "2.{$row['xh']}\t{$row['name']}\t{$row['age']}\n";
}
//绑定数据
echo "========================================利用limit显示两条记录===================================\n";
$ps = $db -> prepare("select * from students limit ?");
$ps -> bindValue(1,2,PDO::PARAM_INT);//把2值绑定到第一个?占位置，数据是整数类型
$ps -> execute();//占位符已经绑定，无需再提供参数
foreach ($ps as $row){
    echo "3.{$row['xh']}\t{$row['name']}\t{$row['age']}\n";
}
echo "========================================使用命名占位符===================================\n";
$ps = $db -> prepare("select * from students limit :offset, :length ");
$ps -> bindValue(':offset',2,PDO::PARAM_INT);
$ps -> bindValue(':length',1,PDO::PARAM_INT);
$ps -> execute();//占位符已经绑定，无需再提供参数
foreach ($ps as $row){
    echo "4.{$row['xh']}\t{$row['name']}\t{$row['age']}\n";
}
echo "========================================绑定变量到占位符===================================\n";
$ps = $db -> prepare("select * from students limit :offset, :length ");
$ps -> bindParam(':offset',$offset,PDO::PARAM_INT);
$ps -> bindParam(':length',$length,PDO::PARAM_INT);
$offset = $length = 1;
$ps -> execute();//占位符已经绑定，无需再提供参数
foreach ($ps as $row){
    echo "5.{$row['xh']}\t{$row['name']}\t{$row['age']}\n";
}
echo "-------------------------------------\n";
$offset = 2;
$ps -> execute();//占位符已经绑定，无需再提供参数
foreach ($ps as $row){
    echo "5.{$row['xh']}\t{$row['name']}\t{$row['age']}\n";
}

echo "========================================绑定变量到结果集的某一列===================================\n";
$ps = $db -> prepare("select * from students limit 1,2");
$ps -> bindColumn(1,$xh);//通过列号绑定
$ps -> bindColumn(2,$name);
$ps -> bindColumn('age',$age,PDO::PARAM_INT);//通过列名绑定
$ps -> execute();//占位符已经绑定，无需再提供参数
foreach ($ps as $row){
    echo "6.{$xh}\t{$name}\t{$age}\n";
}

